var express = require('express');
var router = express.Router();
var {customQuery} = require('../mysql/index')

// 查询message表
router.get('/', async function (req, res, next) {
    try {
        const {limit = 10, page = 1, query = ''} = req.query;
        const offset = (page - 1) * limit;
        const countSql = `SELECT COUNT(*) AS total FROM message WHERE message.user_name LIKE '%${query}%'`;
        const selectSql = `SELECT
                          user_list.head_photo,
                          user_list.real_name,
                          message.*,
                          (SELECT COUNT(*) FROM comment WHERE comment.parent_id = message.id) AS comment_count
                          FROM user_list
                          LEFT JOIN message ON user_list.id = message.user_id
                          WHERE message.user_name LIKE '%${query}%'
                          ORDER BY message.id DESC LIMIT 10 OFFSET ${offset}`;
        const result = await customQuery(countSql);
        const total = result[0].total;
        console.log(111111111111111111,total)
        const res1 = await customQuery(selectSql);
        const results = res1.map(item => {
            item.content = item.content.toString('utf8');
            return item
        });
        console.log(333333333333333,results)
        return res.status(200).send({code: 200, msg: '查询成功', data: {total, list: results}});
    } catch (error) {
        return res.status(200).send({code: 500, msg: '查询失败', error: error});
    }
});


router.get('/test', async function (req, res, next) {


    const {limit = 10, page = 1} = req.query;
    const offset = (page - 1) * limit;

    const selectSql = `
  SELECT
  user_list.head_photo,
  user_list.real_name,
  message.*,
  (SELECT COUNT(*) FROM comment WHERE comment.parent_id = message.id) AS comment_count
  FROM user_list
  LEFT JOIN message ON user_list.id = message.user_id
  ORDER BY message.id DESC LIMIT 10 OFFSET ${offset} `;


//   SELECT
// 	t1.*,
// 	t2.*,
// 	t3.*
// FROM
// 	message t1
// 	LEFT JOIN user_list t2 ON t1.user_id = t2.id
// 	LEFT JOIN COMMENT t3 ON t2.id = t3.user_id
// WHERE
// 	t1 = 'id';


    const result = await customQuery(selectSql);
    return res.status(200).send({code: 200, msg: '查询成功', data: result});
});

// 1.查询 message 表中的所有数据。
// 2.根据 message 表中的 user_id 查询对应的 user_list 表中的 id 数据,查到的数据 存入 userInfo字段里面
// 3.根据 message 表中的 id 查询 comment 表中的对应 parent_id 为该 id 的数据，并将结果存储在一个名为 comment_list 的数组字段中。


// 添加数据到message表
router.post('/add', function (req, res, next) {
    let {content, user_id, user_name} = req.body;
    const regex = /^<p>(&nbsp;\s*)*<\/p>$/;
    console.log('regex.test(content)', regex.test(content))
    if (regex.test(content)) {
        content = '';
    }
    if (!content) {
        return res.status(200).send({code: 400, msg: '请输入内容'});
    }
    console.log('进入add接口', content, user_id, user_name)
    const create_date = new Date().toISOString().slice(0, 19).replace('T', ' ');
    const sql = `INSERT INTO message (content, create_date, user_id, user_name, \`like\`) VALUES ('${content.replaceAll("'", '"')}', '${create_date}', '${user_id}', '${user_name}', 0)`;
    console.log(sql)
    customQuery(sql)
        .then(results => {
            return res.status(200).send({code: 200, msg: '留言成功', list: results});
        })
        .catch(error => {
            return res.status(200).send({code: 500, msg: '留言时出错', error: error});
        });
});

// like接口
router.post('/like', function (req, res, next) {
    let {id, type = 1, message_like_id,user_id} = req.body;
    console.log(1111111111111,id, type, message_like_id)
    const sql = `UPDATE message SET \`like\` = \`like\` ${type === 1 ? '+' : '-'} 1 WHERE id = ${id}`;
    customQuery(sql)
        .then(results => {
            if (message_like_id) {
                const sql2 = `UPDATE user_list SET message_like_id = '${message_like_id}' WHERE id = ${user_id}`;
                customQuery(sql2)
                    .then(results => {
                        return res.status(200).send({code: 200, msg: '操作成功', list: results});
                    })
                    .catch(error => {
                        return res.status(200).send({code: 500, msg: '操作失败', error: error});
                    });
            } else {
                return res.status(200).send({code: 200, msg: '操作成功', list: results});
            }
        })
        .catch(error => {
            return res.status(200).send({code: 500, msg: '操作失败', error: error});
        });
});
// delete接口
router.post('/delete', function (req, res, next) {
    let {id} = req.body;
    const sql = `DELETE FROM message WHERE id = ${id}`;
    customQuery(sql)
        .then(results => {
            const sql2 = `DELETE FROM comment WHERE parent_id = ${id}`;
            customQuery(sql2)
                .then(results => {
                    return res.status(200).send({code: 200, msg: '删除成功', list: results});
                })
                .catch(error => {
                    return res.status(200).send({code: 500, msg: '删除时出错', error: error});
                });
        })
        .catch(error => {
            return res.status(200).send({code: 500, msg: '删除时出错', error: error});
        });
});


module.exports = router;



